package com.winning.ds.common.util;

import com.winning.ds.common.entity.PageBean;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @author w_jx@winning.com.cn
 * @version 1.0.0
 * @ClassName PageUtil.java
 * @Description 分页查询优化
 * @createTime 2020年10月15日 15:21:00
 */
@Slf4j
@Component
public class PageUtil {

    /**
     * 分页查询
     *
     * @param sql 查询sql
     * @param pageBean 分页实体
     * @param params 执行sql参数
     * @param sjklx 数据库类型
     */
    public PageBean queryForPage(PageBean<?> pageBean, String sql, Object[] params, JdbcTemplate jdbcTemplate, String sjklx) {
        log.info("查询数据量的sql： {}", sql);
        String from = StringUtils.substringAfter(sql, "FROM");
        Map<String, Object> map = jdbcTemplate.queryForMap("SELECT  COUNT(1)  AS  COUNT FROM " + from);
        Object obj =  map.get("count");
        Integer count = 0;
        if (obj instanceof BigDecimal) {
            count = ((BigDecimal) obj).intValue();
        }else if ((obj instanceof Long)){
            count = ((Long) obj).intValue();
        }else {
            count = (Integer) obj;
        }

        int offset = (pageBean.getPageNo() - 1) * pageBean.getPageSize() + 1;
        StringBuilder tempBuffer = new StringBuilder(sql);
        if ("sqlserver".equalsIgnoreCase(sjklx)) {
            tempBuffer.insert(0, "select * from (select row_number() over(" + pageBean.getOrderColumn() + ") row_num,t1.* from(")
                    .append(") t1 ) t2")
                    .append(" where t2.row_num >= ")
                    .append(offset).append(" and t2.row_num < ").append(offset + pageBean.getPageSize());
        } else if ("oracle".equalsIgnoreCase(sjklx)) {
            // 计算第一条记录的位置，Oracle分页是通过rownum进行的，而rownum是从1开始的
            tempBuffer.insert(0, "select u.*, rownum r from (").append(") u where rownum < ")
                    .append(offset + pageBean.getPageSize());
            tempBuffer.insert(0, "select * from (").append(")t1 where r >= ").append(offset).append(" ").append(pageBean.getOrderColumn());
            // 上面的Sql语句拼接之后大概是这个样子：
            // select * from (select u.*, rownum r from (select * from t_user ) u
            // where rownum < 31)t1 where r >= 16   order by t1.dlsj desc
        } else if ("mysql".equalsIgnoreCase(sjklx)) {
            // 计算第一条记录的位置，Mysql中记录的位置是从0开始的。
            tempBuffer.append(" limit ").append(offset).append(",").append(pageBean.getPageSize());
        } else if ("greenplum".equalsIgnoreCase(sjklx)) {
            tempBuffer.append(" limit ").append(pageBean.getPageSize()).append(" offset ").append(offset - 1);
        }
        log.info("查询数据量的sql： {}", tempBuffer.toString());
        List list = jdbcTemplate.queryForList(tempBuffer.toString(), params);

        if ("greenplum".equalsIgnoreCase(sjklx)) {
          list = transformUpperCase(list);
        }
        pageBean.setTotalRecord(count);
        String[] array = {"ZJHM", "SFZH"};

        for (Object o : list) {
            Map<String, Object> hashMap = (Map<String, Object>) o;
            for (String key : array) {
                if (hashMap.containsKey(key)) {
                    String zjhm = (String) hashMap.get(key);
                    if(StringUtils.isNotEmpty(zjhm)){
                        zjhm = zjhm.replaceAll("(\\w{4})\\w*(\\w{4})", "$1****$2");
                        hashMap.put(key, zjhm);
                    }
                }
            }
        }
        pageBean.setResults(list);
        return pageBean;
    }


    public static List<Map<String, Object>> transformUpperCase(List<Map<String, Object>> list) {
        for (int i = 0;i < list.size(); i++){
            Map<String, Object> resultMap = new HashMap<>();
            Map<String, Object> map = list.get(i);
            if (map == null || map.isEmpty()) {
                return list;
            }
            Set<String> keySet = map.keySet();
            for (String key : keySet) {
                String newKey = key.toUpperCase();
                resultMap.put(newKey, map.get(key));
            }
            list.set(i,resultMap);
        }
        return list;
    }

}
